package com.alexis.portknocking;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBAdapter {
	private Context ctx;
	private DBOpenHelper helper;
	private SQLiteDatabase db;
	
	public static final String KEY_ID = "_id";
	public static final String KEY_HOST = "host";
	public static final String KEY_PORT = "port";
	public static final String KEY_STARTSTOP = "startstop";
	
	public static final int START_MODE = 0;
	public static final int STOP_MODE = 1;
	
	private static class DBOpenHelper extends SQLiteOpenHelper {
		public DBOpenHelper(Context context) {
			super(context, "hostlist.db", null, 3);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL("CREATE TABLE hosts (_id INTEGER PRIMARY KEY, " +
					"host STRING, " +
					"port INTEGER, " +
					"startstop INTEGER DEFAULT 0," +
					"position INTEGER DEFAULT 0);");
			
			/* Triggers for computing position after DELETE/UPDATE-ing an host. */
			db.execSQL("CREATE TRIGGER host_delete_recalcpos AFTER DELETE ON " +
					"hosts FOR EACH ROW BEGIN UPDATE hosts " +
					"SET position=position-1 WHERE position>old.position; " +
					"END;");
			
			db.execSQL("CREATE TRIGGER host_update_recalcpos BEFORE UPDATE OF position " +
					"ON hosts FOR EACH ROW BEGIN UPDATE hosts " +
					"SET position=old.position WHERE position=new.position; " +
					"END;"); // I love involutive bijections!
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP TABLE IF EXISTS hosts");
			onCreate(db);
		}
	}
	
	public DBAdapter(Context ctx) {
		this.ctx = ctx;
	}
	
	public void open() {
		helper = new DBOpenHelper(this.ctx);
		db = helper.getWritableDatabase();
	}
	
	public void close() {
		db.close();
	}
	
	public int[] getHosts(long startstop) {
		Cursor c = db.query("hosts", 
				new String[]{"_id"}, "startstop="+startstop, null, null, null, "position");
		
		int[] data = new int[c.getCount()];
		c.moveToFirst();
		
		for (int i = 0; i < c.getCount(); i++) {
			data[i] = c.getInt(0);
			c.moveToNext();
		}
		
		c.close();
		
		return data;
	}
	
	public String getHost(int id) {
		Cursor c = db.query("hosts", 
				new String[]{"host"}, "_id ="+id, null, null, null, null);
		String host;
		
		c.moveToFirst();
		host = c.getString(0);
		c.close();
		
		return host;
	}
	
	public int getPort(int id) {
		// TODO: WARNING SQL INJECTION!
		Cursor c = db.query("hosts",
				new String[]{"port"}, "_id = "+id, null, null, null, null);
		c.moveToFirst();
		int result = c.getInt(0);
		c.close();
		
		return result;
	}
	
	public Cursor getRawHosts(int startstop) {
		return db.query("hosts", null, "startstop="+startstop, null, null, null, "position");
	}
	
	public Cursor getRawHost(long id) {
		Cursor c = db.query("hosts", null, "_id = "+String.valueOf(id), null, null, null, null);
		if (c != null)
			c.moveToFirst();
		return c;
	}
	
	public void createHost(String host, Long port, int startstop) {
		db.execSQL("INSERT INTO hosts (host,port,position,startstop) VALUES (?,?,(SELECT " +
				"IFNULL(MAX(position)+1,0) " +
				"FROM hosts WHERE startstop=?),?)", new Object[]{host, port, startstop, startstop});
	}
	
	public void updateHost(long id, String host, Long port) {
		ContentValues args = new ContentValues();
		args.put(KEY_HOST, host);
		args.put(KEY_PORT, port);
		
		db.update("hosts", args, KEY_ID+"="+id, null);
	}
	
	public void deleteHost(long id) {
		db.delete("hosts", KEY_ID+"="+id, null);
	}
	
	public void upHost(long id) {
		db.execSQL("UPDATE hosts SET position = position-1 WHERE _id=?", new Object[]{id});
	}
	
	public void downHost(long id) {
		db.execSQL("UPDATE hosts SET position = position+1 WHERE _id=?", new Object[]{id});
	}
}
